import pandas as pd
import numpy as np
from sklearn.metrics import accuracy_score, mean_squared_error, r2_score, mean_squared_log_error, mean_absolute_error
from sklearn.preprocessing import StandardScaler,MinMaxScaler
from scipy.spatial import distance
from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression, SGDRegressor
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor, AdaBoostRegressor, BaggingRegressor, ExtraTreesRegressor
import sklearn.model_selection as ms
from sklearn.neighbors import KNeighborsRegressor
import xgboost as xgb
from sklearn.linear_model import Lasso, Ridge, ElasticNet
import datetime
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
from SKO.AbstractDPJob import AbstractDPJob
from Predict_FESJob import Predict_FESJob
class Predict_6625Job(AbstractDPJob):
    def __init__(self,
                 p_mode=None,p_st_no=None, p_aim_st_s=None):
        super(Predict_6625Job, self).__init__()
        self.mode = p_mode
        self.st_no = p_st_no
        self.aim_st_s = p_aim_st_s

        pass
    def execute(self):
        return self.do_execute()
    def do_execute(self):
        super(Predict_6625Job, self).do_execute()
        # 预测二炼钢硫平衡接口传入参数
        # 出钢记号、计划钢种目标S
        mode = self.mode
        st_no = self.st_no
        aim_st_s = self.aim_st_s
        msg = ''

        if mode == 2:
            if st_no == 'IH2554A2':
                kr_s_aim = 2.4
            elif st_no[:2] == 'IH' and st_no != 'IH2554A2':
                kr_s_aim = 1.4
            elif aim_st_s <= 15:
                kr_s_aim = 1.4
            elif aim_st_s <= 20 and aim_st_s > 15:
                kr_s_aim = 2.4
            elif aim_st_s <= 30 and aim_st_s > 20:
                kr_s_aim = 5.4
            elif aim_st_s <= 50 and aim_st_s > 30:
                kr_s_aim = 1.4
            elif aim_st_s <= 100 and aim_st_s > 50:
                kr_s_aim = 2.4
            elif aim_st_s < 150 and aim_st_s > 100:
                kr_s_aim = 3.4
            elif aim_st_s < 180 and aim_st_s >= 150:
                kr_s_aim = 5.4
            elif aim_st_s <= 250 and aim_st_s >= 180:
                kr_s_aim = 5.4
            else:
                kr_s_aim = 5.4
            pret_s_aim = int(kr_s_aim * 10)
        else:


            DB_HOST_MPP_DB2 = '10.70.48.41'
            DB_PORT_MPP_DB2 = 50021
            DB_DBNAME_MPP_DB2 = 'BGBDPROD'
            DB_USER_MPP_DB2 = 'm1admin'
            DB_PASSWORD_MPP_DB2 = 'm1adminbdzg'

            def getConnectionDb2(host, port, dbname, user, password):
                # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
                engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                                       encoding="utf-8", poolclass=NullPool)
                return engine.connect()

            # db_conn_mpp = getConnectionDb2(DB_HOST_MPP_DB2,
            #                                DB_PORT_MPP_DB2,
            #                                DB_DBNAME_MPP_DB2,
            #                                DB_USER_MPP_DB2,
            #                                DB_PASSWORD_MPP_DB2)

            # 根据出钢记号、计划钢种目标S，进行分组
            if st_no == 'IH2554A2':
                group = 1
                sql_condition = " AND ST_NO = 'IH2554A2'"
            elif st_no[:2] == 'IH' and st_no != 'IH2554A2':
                group = 2
                sql_condition = " AND ST_NO != 'IH2554A2' AND left(ST_NO,2) = 'IH' "
            elif st_no[:2] == 'IW':
                group = 3
                sql_condition = " AND left(ST_NO,2) = 'IW' "
            elif aim_st_s <= 15 and st_no[:2] not in ['IH', 'IW']:
                group = 4
                sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S <= 15"
            elif aim_st_s <= 20 and aim_st_s > 15 and st_no[:2] not in ['IH', 'IW']:
                group = 5
                sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 15 AND AIM_ST_S <= 20"
            elif aim_st_s <= 30 and aim_st_s > 20 and st_no[:2] not in ['IH', 'IW']:
                group = 6
                sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 20 AND AIM_ST_S <= 30"
            elif aim_st_s <= 50 and aim_st_s > 30 and st_no[:2] not in ['IH', 'IW']:
                group = 7
                sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 30 AND AIM_ST_S <= 50"
            elif aim_st_s <= 100 and aim_st_s > 50 and st_no[:2] not in ['IH', 'IW']:
                group = 8
                sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 50 AND AIM_ST_S <= 100"
            elif aim_st_s < 150 and aim_st_s > 100 and st_no[:2] not in ['IH', 'IW']:
                group = 9
                sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 100 AND AIM_ST_S < 150"
            elif aim_st_s < 180 and aim_st_s >= 150 and st_no[:2] not in ['IH', 'IW']:
                group = 10
                sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S >= 150 AND AIM_ST_S < 180"
            elif aim_st_s <= 250 and aim_st_s >= 180 and st_no[:2] not in ['IH', 'IW']:
                group = 11
                sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S >= 180 AND AIM_ST_S <= 250"
            else:
                group = 12
                sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 250"
            #本地编写目前读取本地文件，后续使用sql_condition进行拼接SQL
            start = datetime.datetime.now()
            delta_day2 = 10
            delta_day1 = delta_day2 + 365
            p_day_2 = (start - datetime.timedelta(days=delta_day2)).strftime("%Y%m%d")
            p_day_1 = (start - datetime.timedelta(days=delta_day1)).strftime("%Y%m%d")
            sql = " SELECT PROD_DATE,ST_NO,AIM_ST_S,PRET_S_AIM " \
                  " FROM BGTAMOMMSM.T_ODS_TMMSM0701  " \
                  " WHERE TC_PROC_FlAG = 2 AND PROD_DATE>='%s' " \
                  " AND PROD_DATE<'%s' " \
                  " %s ORDER BY PROD_DATE " % (p_day_1, p_day_2, sql_condition)
            print(sql)
            # df = pd.read_sql_query(sql, con=db_conn_mpp)



            xlsx_name = 'D:/repos/sicost/group' + str(int(group)) + '_new.xlsx'
            df = pd.read_excel(xlsx_name)
            if df.empty is True:
                msg = '该钢种无分组'
            df.columns = df.columns.str.upper()
            df['PROD_DATE'] = df['PROD_DATE'].astype(str)
            start = datetime.datetime.now()
            #使用近期实绩数据的众数来作为该分组的fret_s_aim预处理目标S,
            for i in range(0, 10):
                mode_fret_s_aim = 0
                delta_day = int((i + 1) * 10)
                p_day_1 = (start - datetime.timedelta(days=delta_day)).strftime("%Y%m%d")
                print(p_day_1)
                df1 = df[(df['PROD_DATE'] >= p_day_1)]
                if df1.empty is False:
                    mode_fret_s_aim = df['PRET_S_AIM'].mode()[0]
                    break
                else:
                    continue
            print(mode_fret_s_aim)
            # pret_s_aim = int(mode_fret_s_aim * 10)
            pret_s_aim = int(mode_fret_s_aim)

        return msg, pret_s_aim